import os
import re
import csv
import json
import logging
import threading
import time
import configparser
from pathlib import Path
from pymediainfo import MediaInfo as PyMediaInfo
from typing import List, Dict, Optional, Tuple
from concurrent.futures import ThreadPoolExecutor, as_completed
from tqdm import tqdm

# 导入数据模型
from models import AlbumInfo, MediaInfo, UploadTask, ScanResult

# 导入其他模块
from centos_cos import CosUploader
from database import DatabaseManager

logger = logging.getLogger('MediaScanner')


class MediaScanner:
    # 支持的媒体文件类型
    VIDEO_FORMATS = ['.mp4', '.avi', '.mov', '.mkv', '.flv', '.wmv']
    AUDIO_FORMATS = ['.mp3', '.wav', '.ogg', '.flac', '.m4a', '.aac']
    ALL_MEDIA_FORMATS = VIDEO_FORMATS + AUDIO_FORMATS

    def __init__(self, base_path: str, config_file: Optional[str] = None):

        # 从配置文件加载配置
        self.config = self.load_config(config_file)

        """
             初始化媒体扫描器

             :param base_path: 媒体目录基础路径
             :param config_file: 配置文件路径（可选）
             """
        self.base_path = Path(self.config['scanner'].get('base_path', '')).resolve()

        # 自动从路径中提取分类名称
        self.category_name = self.base_path.name
        self.scan_result = ScanResult(category_name=self.category_name)

        # 初始化数据库管理器（如果配置中有数据库信息）
        self.db_manager = None
        # print(self.config)
        if self.config.get('database'):
            self.db_manager = DatabaseManager(
                host=self.config['database'].get('host', 'localhost'),
                user=self.config['database'].get('user', 'root'),
                password=self.config['database'].get('password', ''),
                database=self.config['database'].get('database', 'chinese_poetry'),
                port=int(self.config['database'].get('port', 3306))
            )

        # 初始化腾讯云上传器
        self.cos_uploader = CosUploader(
            secret_id=self.config.get('cos', {}).get('secret_id'),
            secret_key=self.config.get('cos', {}).get('secret_key'),
            region=self.config.get('cos', {}).get('region', 'ap-guangzhou'),
            bucket=self.config.get('cos', {}).get('bucket', 'mengyao-poetry-1358510944')
        )
        # 添加批量处理计数器
        self.batch_counter = 0
        self.batch_lock = threading.Lock()  # 线程安全的锁
        self.batch_size = 10  # 每处理10个文件执行一次数据库操作
        self.folder_prefix = self.config['scanner'].get('folder_prefix', '')
        self.media_type = self.config['scanner'].get('media_type', '')

    def load_config(self, config_file: Optional[str] = None) -> dict:
        """加载配置文件，支持INI格式"""
        config = {
            'database': {},
            'cos': {},
            'scanner': {}
        }

        if not config_file:
            logger.info("未提供配置文件，使用默认配置")
            return config

        config_path = Path(config_file)
        if not config_path.exists():
            logger.warning(f"配置文件不存在: {config_path}")
            return config

        try:
            # 创建配置解析器
            parser = configparser.ConfigParser()
            # 尝试用UTF-8编码读取文件
            try:
                with open(config_path, 'r', encoding='utf-8') as f:
                    parser.read_file(f)
            except UnicodeDecodeError:
                # 如果UTF-8失败，尝试GBK编码
                try:
                    with open(config_path, 'r', encoding='gbk') as f:
                        parser.read_file(f)
                except Exception as e:
                    logger.error(f"无法读取配置文件: {e}")
                    return config

            # 读取数据库配置
            if 'database' in parser:
                config['database'] = {
                    'host': parser.get('database', 'host', fallback='localhost'),
                    'port': parser.get('database', 'port', fallback='3306'),
                    'user': parser.get('database', 'user', fallback='root'),
                    'password': parser.get('database', 'password', fallback=''),
                    'database': parser.get('database', 'database', fallback='media_db')
                }
            else:
                raise configparser.NoSectionError('缺少database配置')

            # 读取COS配置
            if 'cos' in parser:
                config['cos'] = {
                    'secret_id': parser.get('cos', 'secret_id', fallback=''),
                    'secret_key': parser.get('cos', 'secret_key', fallback=''),
                    'region': parser.get('cos', 'region', fallback='ap-guangzhou'),
                    'bucket': parser.get('cos', 'bucket', fallback='mengyao-poetry-1358510944')
                }
            else:
                raise configparser.NoSectionError('缺少cos配置')

            # 读取扫描器配置
            if 'scanner' in parser:
                config['scanner'] = {
                    'base_path': parser.get('scanner', 'base_path', fallback=''),
                    'upload_batch_size': parser.get('scanner', 'upload_batch_size', fallback=''),
                    'folder_prefix': parser.get('scanner', 'folder_prefix', fallback=''),
                    'media_type': parser.get('scanner', 'media_type', fallback='video'),
                }
            else:
                raise configparser.NoSectionError('缺少scanner配置')

            logger.info(f"成功加载配置文件: {config_path}")
            return config
        except Exception as e:
            logger.error(f"加载配置文件失败: {e}")
            return config

    def load_upload_status_from_db(self):
        """从数据库加载已上传状态"""
        if not self.db_manager:
            logger.info("数据库未配置，无法加载上传状态")
            return False

        try:
            # 获取分类ID
            category_id = self.get_category_id()
            if not category_id:
                logger.info("分类不存在于数据库，无需加载上传状态")
                return

            # 查询所有媒体的上传状态
            sql = """
                   SELECT m.file_path, m.is_uploaded
                   FROM media m
                   JOIN album a ON m.album_id = a.id
                   WHERE a.category_id = %s
               """
            if not self.db_manager.connect():
                logger.error("无法连接到数据库")
                return

            self.db_manager.cursor.execute(sql, (category_id,))
            rows = self.db_manager.cursor.fetchall()

            # 创建文件路径到上传状态的映射
            upload_status_map = {row['file_path']: bool(row['is_uploaded']) for row in rows}

            # 更新扫描结果中的上传状态
            for album_name, album_info in self.scan_result.album_id_map.items():
                for media in album_info.media:
                    if media.file_path in upload_status_map:
                        media.is_uploaded = upload_status_map[media.file_path]

            # 更新上传任务中的上传状态
            for task in self.scan_result.upload_tasks:
                if task.file_path in upload_status_map:
                    task.is_uploaded = upload_status_map[task.file_path]

            logger.info(f"从数据库加载了 {len(rows)} 条媒体的上传状态")
            return True
        except Exception as e:
            logger.error(f"加载上传状态失败: {e}")
            return False
        # finally:
        #     if self.db_manager and self.db_manager.connection:
        #         self.db_manager.connection.close()

    def get_category_id(self) -> Optional[int]:
        """获取分类ID"""
        if not self.db_manager:
            return None

        try:
            if not self.db_manager.connect():
                logger.error("无法连接到数据库")
                return None

            sql = "SELECT id FROM category WHERE name = %s and media_type = %s;"
            self.db_manager.cursor.execute(sql, (self.category_name, self.media_type,))
            result = self.db_manager.cursor.fetchone()
            return result['id'] if result else None
        except Exception as e:
            logger.error(f"获取分类ID失败: {e}")
            return None

    def parse_album_info(self, dir_name: str) -> Tuple[Optional[str], Optional[int], int, str]:
        """解析专辑目录名称信息和排序号"""
        # 支持格式: 01-专辑名称-159集 (带数字前缀)
        pattern = r"^(\d{2})[-_\s]*(.+?)(?:[-_\s](\d+)集)?$"

        # 尝试匹配格式1
        match1 = re.match(pattern, dir_name)
        if match1:
            try:
                # 提取顺序号
                order_number = int(match1.group(1))
                album_name = match1.group(2).strip()

                # 提取集数（如果存在）
                episode_count = int(match1.group(3)) if match1.group(3) else None

                return album_name, episode_count, order_number, dir_name
            except ValueError:
                pass

        # 无法识别格式，使用目录名作为专辑名
        album_name = dir_name
        return album_name, None, 0, dir_name

    def parse_media_info(self, filename: Path) -> MediaInfo:
        """从文件名解析媒体信息和排序号"""
        # 提取文件格式
        file_format = filename.suffix.lower()

        # 确定媒体类型
        media_type = 'video' if file_format in self.VIDEO_FORMATS else 'audio'

        # 去除扩展名
        stem = filename.stem

        # 尝试解析序号 (如"第1集"或"01")
        seq_match = re.search(r'[第]?(\d+)[集节]?', stem)
        sequence = int(seq_match.group(1)) if seq_match else 0

        # 使用文件名作为标题
        title = stem

        return MediaInfo(
            title=title,
            sequence=sequence,
            media_type=media_type,
            file_format=file_format,
            file_path="",
            local_path=str(filename),
            sort_idx=0
        )

    def generate_import_sql(self) -> List[str]:
        """生成数据库导入SQL语句"""
        self.scan_result.category_media_type = self.media_type

        # 重置SQL语句列表
        sql_statements = []

        # 事务开始
        sql_statements.append("START TRANSACTION;")

        category_check_sql = (
            f"SET @category_exists = (SELECT COUNT(*) FROM category WHERE name = '{self.scan_result.category_name}' and media_type='{self.scan_result.category_media_type}');"
        )
        sql_statements.append(category_check_sql)

        # 插入分类
        category_sql = (
            f"INSERT INTO category (name, media_type) "
            f"SELECT '{self.scan_result.category_name}', '{self.scan_result.category_media_type}' "
            f"WHERE @category_exists = 0;"
        )
        sql_statements.append(category_sql)

        # 获取分类ID
        category_id_sql = (
            f"SET @category_id = (SELECT id FROM category WHERE name = '{self.scan_result.category_name}' and media_type='{self.scan_result.category_media_type}');"
        )
        sql_statements.append(category_id_sql)
        sql_statements.append("")

        # 处理专辑（按顺序排序）
        album_names = sorted(
            self.scan_result.album_id_map.keys(),
            key=lambda k: self.scan_result.album_order_map[k]
        )
        for album_name in album_names:
            album_info = self.scan_result.album_id_map[album_name]
            album_order = self.scan_result.album_order_map[album_name]

            # 为每个专辑生成检查语句
            album_check_sql = (
                f"SET @album_{album_name.replace(' ', '_').replace('-', '_')}_exists = "
                f"(SELECT COUNT(*) FROM album WHERE name = '{album_name}' AND category_id = @category_id);"
            )
            sql_statements.append(album_check_sql)

            # 插入专辑（只在不存在时插入）
            album_sql = (
                f"INSERT INTO album (category_id, name, original_name, `order`, "
                f"episode_count, media_type) "
                f"SELECT @category_id, '{album_name}', '{album_info.original_name}', "
                f"{album_order}, "
                f"{album_info.episode_count if album_info.episode_count is not None else 'NULL'}, "
                f"'{album_info.media_type}' "
                f"WHERE @album_{album_name.replace(' ', '_').replace('-', '_')}_exists = 0;"
            )
            sql_statements.append(album_sql)

            # 获取专辑ID
            album_id_sql = (
                f"SET @{album_name.replace(' ', '_').replace('-', '_')}_id = "
                f"(SELECT id FROM album WHERE name = '{album_name}' and media_type='{album_info.media_type}');"
            )
            sql_statements.append(album_id_sql)
        sql_statements.append("")

        # 处理多媒体资源（按顺序排序）
        for album_name in album_names:
            album_info = self.scan_result.album_id_map[album_name]
            album_var_name = album_name.replace(' ', '_').replace('-', '_')

            # 对媒体资源按提取的序号排序
            media_sorted = sorted(
                album_info.media,
                key=lambda m: m.sequence
            )

            # 添加媒体排序信息
            for order, media in enumerate(media_sorted, start=1):
                media.order = order

            for media in media_sorted:
                title = media.title.replace("'", "\\'")
                file_path = media.file_path.replace("'", "\\'")
                file_size = media.file_size if media.file_size is not None else 'NULL'
                duration = media.duration if media.duration is not None else 0
                file_path = self.folder_prefix + file_path

                # 使用专辑ID变量
                media_sql = (
                    f"INSERT INTO media (album_id, title, episode_seq, `order`, "
                    f"file_path, media_type, file_format, file_size,duration, is_uploaded) "
                    f"VALUES (@{album_var_name}_id, '{title}', {media.sequence}, {media.order}, "
                    f"'{file_path}', '{media.media_type}', '{media.file_format}', {file_size}, {duration}, "
                    f"{1 if media.is_uploaded else 0}) "
                    f"ON DUPLICATE KEY UPDATE title=VALUES(title), episode_seq=VALUES(episode_seq), "
                    f"`order`=VALUES(`order`), file_path=VALUES(file_path), "
                    f"media_type=VALUES(media_type), file_format=VALUES(file_format), "
                    f"file_size=VALUES(file_size),duration=VALUES(duration), is_uploaded=VALUES(is_uploaded);"
                )
                sql_statements.append(media_sql)
        # 提交事务
        sql_statements.append("COMMIT;")

        return sql_statements

    def get_media_duration(self, file_path: str) -> float:
        """
        获取媒体文件（视频/音频）的播放时长（秒）

        :param file_path: 媒体文件路径
        :return: 播放时长（秒），如果获取失败返回0
        """
        try:
            media_info = PyMediaInfo.parse(file_path)

            # 查找视频轨道或音频轨道
            for track in media_info.tracks:
                if track.track_type in ['Video', 'Audio', 'General']:
                    if hasattr(track, 'duration') and track.duration:
                        # 持续时间以毫秒为单位
                        return float(track.duration) / 1000.0

            logger.warning(f"无法在媒体文件中找到时长信息: {file_path}")
            return 0.0
        except Exception as e:
            logger.warning(f"获取媒体时长失败: {file_path} - {str(e)}")
            return 0.0

    def scan_directory(self):
        """
        扫描目录并收集数据

        :param test_files: 指定要扫描的测试文件列表（可选）
        """
        logger.info(f"开始扫描目录: {self.base_path}")
        logger.info(f"自动分类名称: {self.scan_result.category_name}")

        # 确保是目录
        if not self.base_path.is_dir():
            raise ValueError(f"路径不是目录: {self.base_path}")

        # 统计变量
        album_count = 0
        media_count = 0

        # 收集所有专辑目录
        album_dirs = []
        for entry in self.base_path.iterdir():
            if entry.is_dir():
                album_dirs.append(entry)

        # 按目录名自然排序
        album_dirs.sort(key=lambda d: self.natural_sort_key(d.name))

        # 给无数字前缀的专辑分配顺序号（1000+）
        next_default_order = 1001

        # 遍历专辑目录
        for album_dir in album_dirs:
            # 解析专辑信息
            album_name, album_episodes, order_number, original_name = self.parse_album_info(album_dir.name)
            if not album_name:
                logger.info(f"  跳过无法解析的目录: {album_dir.name}")
                continue

            # 处理无顺序号的专辑
            if order_number == 0:
                order_number = next_default_order
                next_default_order += 1
                logger.info(f"  自动分配顺序号: {order_number}")

            album_count += 1
            logger.info(
                f"\n[{album_count}] 处理专辑: {album_name} "
                f"(顺序: {order_number}, 集数: {album_episodes if album_episodes else '未知'})"
            )

            # 保存专辑排序信息
            self.scan_result.album_order_map[album_name] = order_number

            # 初始化专辑媒体类型计数
            album_media = {'video': 0, 'audio': 0}

            # 记录专辑信息
            self.scan_result.album_id_map[album_name] = AlbumInfo(
                name=album_name,
                original_name=original_name,
                order=order_number,
                episode_count=album_episodes,
                path=str(album_dir),
                media_types=set()
            )

            # 扫描多媒体文件
            media_files = []
            for item in album_dir.iterdir():
                if item.is_file() and item.suffix.lower() in self.ALL_MEDIA_FORMATS:
                    media_files.append(item)

            logger.debug(f"排序文件列表: {[f.name for f in media_files]}")

            # 按文件名排序（自然排序）
            media_files.sort(key=lambda f: self.natural_sort_key(f.name))

            # 更新实际集数
            actual_episodes = len(media_files)
            if album_episodes and actual_episodes != album_episodes:
                logger.warning(f"  警告: 目录声明集数为 {album_episodes}, 但实际扫描到 {actual_episodes} 个媒体文件")

            # 处理媒体文件
            for media_idx, media_file in enumerate(media_files, start=1):
                media_count += 1
                file_info = self.parse_media_info(media_file)
                file_path = self.folder_prefix + f"{self.scan_result.category_name}/{album_name}/{media_file.name}"

                # 更新媒体信息
                file_info.file_path = file_path
                file_info.sort_idx = media_idx
                file_info.album_name = album_name
                file_info.file_size = media_file.stat().st_size
                file_info.duration = self.get_media_duration(str(media_file))

                # 记录文件类型
                album_media[file_info.media_type] += 1
                self.scan_result.album_id_map[album_name].media_types.add(file_info.media_type)

                # 添加到专辑
                self.scan_result.album_id_map[album_name].media.append(file_info)

                # 添加上传任务
                self.scan_result.upload_tasks.append(UploadTask(
                    album_name=album_name,
                    local_path=str(media_file),
                    remote_key=file_path,
                    file_path=file_path,
                    album_order=order_number,
                    media_order=media_idx,
                    media_type=file_info.media_type,
                    file_size=media_file.stat().st_size,
                    duration=file_info.duration
                ))

                # 每100个文件打印一次进度
                if media_count % 100 == 0:
                    logger.info(f"  已扫描: {media_count} 个媒体文件...")

                # 调试模式只打印前3个
                if media_idx <= 3 or media_idx == len(media_files):
                    media_type_symbol = '视频' if file_info.media_type == 'video' else '音频'
                    logger.info(
                        f"    #{media_idx}{media_type_symbol}: {media_file.name} "
                        f"(类型: {file_info.media_type.upper()}, 序号: {file_info.sequence})"
                    )

            self.scan_result.album_id_map[album_name].media_type = self.media_type
            logger.info(f"  检测到媒体类型: {self.media_type} "
                        f"(视频: {album_media['video']}, 音频: {album_media['audio']})")

        # 保存总数
        self.scan_result.total_albums = album_count
        self.scan_result.total_media = media_count
        logger.info(f"\n扫描完成! 共发现 {album_count} 个专辑, {media_count} 个媒体文件")

        # 从数据库加载已上传状态
        if self.db_manager:
            self.load_upload_status_from_db()

        return self.scan_result

    def natural_sort_key(self, s):
        """
        改进的自然排序键函数，模拟Windows资源管理器的排序行为
        """
        # 特殊处理：确保空格不影响排序（统一替换为固定字符）
        s = s.replace(' ', '_')

        # 分割字符串：数字部分和非数字部分交替
        parts = re.split(r'(\d+)', s)

        # 转换处理：数字部分转整数，非数字部分保持小写
        return tuple(
            int(part) if part.isdigit() else part.lower()
            for part in parts if part  # 过滤空字符串
        )

    def execute_upload_tasks(self, batch_size=2, max_workers=5) -> List[Dict]:
        """执行上传任务，支持分批上传、测试模式和多线程"""
        if not self.cos_uploader.client:
            logger.error("无法执行上传任务，COS客户端未初始化")
            return []

        # 幂等执行：跳过已上传的任务
        tasks_to_upload = [
            task for task in self.scan_result.upload_tasks
            if not task.is_uploaded
        ]

        if not tasks_to_upload:
            logger.info("所有文件已上传，无需执行上传任务")
            return []

        # 按专辑顺序排序
        tasks_to_upload.sort(key=lambda x: (x.album_order, x.media_order))

        logger.info(f"准备上传 {len(tasks_to_upload)} 个文件 (使用{max_workers}个线程)")
        start_time = time.time()
        success_count = 0
        error_count = 0

        results = []
        pending_batch = []  # 待处理的批量任务

        # 创建线程池
        with ThreadPoolExecutor(max_workers=max_workers) as executor:
            # 提交所有上传任务
            future_to_task = {
                executor.submit(
                    self._upload_single_task,
                    task
                ): task for task in tasks_to_upload
            }

            # 处理完成的任务
            for future in as_completed(future_to_task):
                task = future_to_task[future]
                try:
                    result = future.result()
                    # 添加到结果列表
                    results.append(result)
                    if result['status'] == 'success':
                        success_count += 1
                        task.is_uploaded = True

                        # 更新数据库状态
                        if self.db_manager:
                            self.db_manager.connect()
                            self.db_manager.update_upload_status(task.file_path, 1)

                        # 更新媒体记录状态
                        for media in self.scan_result.album_id_map[task.album_name].media:
                            if media.file_path == task.file_path:
                                media.is_uploaded = True
                                break

                        # 添加到待处理批次
                        pending_batch.append(task)

                        # 每处理10个文件执行一次数据库操作
                        with self.batch_lock:  # 使用锁保证线程安全
                            self.batch_counter += 1
                            if self.batch_counter % self.batch_size == 0:
                                self._process_pending_batch(pending_batch)
                                pending_batch = []  # 重置批次
                    else:
                        error_count += 1
                        task.error = result.get('error', '未知错误')
                        self.scan_result.failed_uploads.append(task)
                except Exception as e:
                    error_count += 1
                    task.error = str(e)
                    self.scan_result.failed_uploads.append(task)
                    logger.error(f"任务处理异常: {str(e)}")

        # 处理剩余的批次（不足10个）
        if pending_batch:
            self._process_pending_batch(pending_batch)

        # 计算耗时
        elapsed_time = time.time() - start_time
        minutes, seconds = divmod(elapsed_time, 60)

        logger.info(f"上传完成: 成功 {success_count} 个, 失败 {error_count} 个")
        logger.info(f"总耗时: {int(minutes)}分{seconds:.2f}秒")

        # 如果有上传失败，保存到文件
        if self.scan_result.failed_uploads:
            self.save_failed_uploads()

        return results

    def _process_pending_batch(self, pending_batch: List[UploadTask]):
        """处理待处理的批量任务，更新数据库状态"""
        if not pending_batch:
            return

        logger.info(f"准备批量更新 {len(pending_batch)} 个文件的上传状态到数据库...")

        try:
            if self.db_manager:
                # 批量更新数据库
                self.db_manager.connect()
                for task in pending_batch:
                    self.db_manager.update_upload_status(task.file_path, 1)
                logger.info(f"成功批量更新 {len(pending_batch)} 个文件的上传状态")
            else:
                logger.warning("数据库未配置，无法批量更新上传状态")
        except Exception as e:
            logger.error(f"批量更新上传状态失败: {e}")
            # 标记为失败，以便后续重试
            for task in pending_batch:
                task.is_uploaded = False
                self.scan_result.failed_uploads.append(task)

    def save_failed_uploads(self, output_dir=None):
        """保存失败的上传任务"""
        if not self.scan_result.failed_uploads:
            return

        # 自动确定输出目录
        if output_dir is None:
            output_dir = self.base_path.parent / f"{self.scan_result.category_name}"
        else:
            output_dir = Path(output_dir)

        output_dir.mkdir(parents=True, exist_ok=True)

        # 保存CSV
        csv_path = output_dir / "failed_uploads.csv"
        with open(csv_path, 'w', encoding='utf-8', newline='') as f:
            fieldnames = ['album_order', 'album_name', 'media_order', 'media_type',
                          'local_path', 'remote_key', 'file_path', 'error']
            writer = csv.DictWriter(f, fieldnames=fieldnames)
            writer.writeheader()

            for task in self.scan_result.failed_uploads:
                writer.writerow({
                    'album_order': task.album_order,
                    'album_name': task.album_name,
                    'media_order': task.media_order,
                    'media_type': task.media_type,
                    'local_path': task.local_path,
                    'remote_key': task.remote_key,
                    'file_path': task.file_path,
                    'error': getattr(task, 'error', '未知错误')
                })

        logger.warning(f"保存 {len(self.scan_result.failed_uploads)} 个失败任务到: {csv_path}")

    def import_to_database(self):
        """直接导入数据到数据库"""
        if not self.db_manager:
            logger.error("数据库管理器未初始化，无法导入数据")
            return False

        # 生成SQL语句
        sql_statements = self.generate_import_sql()

        # 执行导入
        logger.info("开始导入数据到数据库...")
        return self.db_manager.execute_import(sql_statements)

    def _upload_single_task(self, task):
        """单文件上传任务，可在多线程中执行"""
        try:
            result = self.cos_uploader.upload_file(
                local_file_path=task.local_path,
                cos_key=task.file_path
            )
            return result
        except Exception as e:
            logger.error(f"文件上传失败: {task.local_path} - {str(e)}")
            return {
                'status': 'error',
                'error': str(e),
                'file_path': task.file_path
            }
